using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;

namespace Haidou.Wpf.Domian.Todo
{
    public class TodoRepository
    {
        private readonly string _dbPath;
        public TodoRepository(string? baseDir = null)
        {
            baseDir ??= Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "HaidouApp");
            if (!Directory.Exists(baseDir)) Directory.CreateDirectory(baseDir);
            _dbPath = Path.Combine(baseDir, "todo.db");
            Initialize().GetAwaiter().GetResult();
        }

        private SqliteConnection GetConnection() => new($"Data Source={_dbPath}");

        private async Task Initialize()
        {
            await using var conn = GetConnection();
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = @"CREATE TABLE IF NOT EXISTS todos(
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Title TEXT NOT NULL,
                Description TEXT,
                CreatedAt TEXT NOT NULL,
                DueTime TEXT,
                IsCompleted INTEGER NOT NULL,
                ReminderIntervalTicks INTEGER,
                NextReminderTime TEXT,
                Channels INTEGER NOT NULL,
                MusicPath TEXT
            );";
            await cmd.ExecuteNonQueryAsync();

            // Migration for updated flag enum (old StrongShake=3, Voice=4 -> new 4,8)
            try
            {
                var mig = conn.CreateCommand();
                // Detect if any channel values equal 3 or 4 without having bit pattern
                mig.CommandText = "UPDATE todos SET Channels=4 WHERE Channels=3"; // StrongShake
                await mig.ExecuteNonQueryAsync();
                mig.CommandText = "UPDATE todos SET Channels=8 WHERE Channels=4"; // Voice (only if previously 4 and not updated above)
                await mig.ExecuteNonQueryAsync();
            }
            catch { /* ignore */ }
        }

        public async Task<long> AddAsync(TodoItem item)
        {
            await using var conn = GetConnection();
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = @"INSERT INTO todos(Title,Description,CreatedAt,DueTime,IsCompleted,ReminderIntervalTicks,NextReminderTime,Channels,MusicPath)
                                VALUES ($t,$d,$c,$due,$cpt,$ri,$nr,$ch,$mp); SELECT last_insert_rowid();";
            cmd.Parameters.AddWithValue("$t", item.Title);
            cmd.Parameters.AddWithValue("$d", item.Description);
            cmd.Parameters.AddWithValue("$c", item.CreatedAt.ToString("o"));
            cmd.Parameters.AddWithValue("$due", (object?)item.DueTime?.ToString("o") ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$cpt", item.IsCompleted ? 1 : 0);
            cmd.Parameters.AddWithValue("$ri", (object?)item.ReminderInterval?.Ticks ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$nr", (object?)item.NextReminderTime?.ToString("o") ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$ch", (int)item.Channels);
            cmd.Parameters.AddWithValue("$mp", (object?)item.MusicPath ?? DBNull.Value);
            var id = (long)(await cmd.ExecuteScalarAsync() ?? 0);
            item.Id = id;
            return id;
        }

        public async Task UpdateAsync(TodoItem item)
        {
            await using var conn = GetConnection();
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = @"UPDATE todos SET Title=$t,Description=$d,DueTime=$due,IsCompleted=$cpt,ReminderIntervalTicks=$ri,NextReminderTime=$nr,Channels=$ch,MusicPath=$mp WHERE Id=$id";
            cmd.Parameters.AddWithValue("$t", item.Title);
            cmd.Parameters.AddWithValue("$d", item.Description);
            cmd.Parameters.AddWithValue("$due", (object?)item.DueTime?.ToString("o") ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$cpt", item.IsCompleted ? 1 : 0);
            cmd.Parameters.AddWithValue("$ri", (object?)item.ReminderInterval?.Ticks ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$nr", (object?)item.NextReminderTime?.ToString("o") ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$ch", (int)item.Channels);
            cmd.Parameters.AddWithValue("$mp", (object?)item.MusicPath ?? DBNull.Value);
            cmd.Parameters.AddWithValue("$id", item.Id);
            await cmd.ExecuteNonQueryAsync();
        }

        public async Task<List<TodoItem>> GetAllAsync()
        {
            var list = new List<TodoItem>();
            await using var conn = GetConnection();
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM todos ORDER BY IsCompleted, DueTime";
            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                list.Add(Read(reader));
            }
            return list;
        }

        public async Task<List<TodoItem>> GetDueRemindersAsync(DateTime nowUtc)
        {
            var list = new List<TodoItem>();
            await using var conn = GetConnection();
            await conn.OpenAsync();
            var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM todos WHERE IsCompleted=0 AND NextReminderTime IS NOT NULL AND NextReminderTime <= $now";
            cmd.Parameters.AddWithValue("$now", nowUtc.ToString("o"));
            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync()) list.Add(Read(reader));
            return list;
        }

        private static TodoItem Read(SqliteDataReader r) => new()
        {
            Id = r.GetInt64(r.GetOrdinal("Id")),
            Title = r.GetString(r.GetOrdinal("Title")),
            Description = r.IsDBNull(r.GetOrdinal("Description")) ? string.Empty : r.GetString(r.GetOrdinal("Description")),
            CreatedAt = DateTime.Parse(r.GetString(r.GetOrdinal("CreatedAt"))).ToUniversalTime(),
            DueTime = r.IsDBNull(r.GetOrdinal("DueTime")) ? null : DateTime.Parse(r.GetString(r.GetOrdinal("DueTime"))).ToUniversalTime(),
            IsCompleted = r.GetInt32(r.GetOrdinal("IsCompleted")) == 1,
            ReminderInterval = r.IsDBNull(r.GetOrdinal("ReminderIntervalTicks")) ? null : TimeSpan.FromTicks(r.GetInt64(r.GetOrdinal("ReminderIntervalTicks"))),
            NextReminderTime = r.IsDBNull(r.GetOrdinal("NextReminderTime")) ? null : DateTime.Parse(r.GetString(r.GetOrdinal("NextReminderTime"))).ToUniversalTime(),
            Channels = (ReminderChannel)r.GetInt32(r.GetOrdinal("Channels")),
            MusicPath = r.IsDBNull(r.GetOrdinal("MusicPath")) ? null : r.GetString(r.GetOrdinal("MusicPath"))
        };
    }
}
